from sqlalchemy.sql import func, desc
from sqlalchemy import UniqueConstraint, text
from flask import url_for
from flask_login import UserMixin
from werkzeug.security import check_password_hash, generate_password_hash
from werkzeug.utils import cached_property
import os
from pathlib import Path
import hashlib
from datetime import datetime
from shutil import move
from .config import Config
from .const import RedisKey
from .extensions import db
from .plugins import cache_utils as cache
from .plugins.image_utils import get_thumbnail_size, gif2webp
from .plugins.image_apis import delete_image_file, get_image_url, get_thumb_url
# import pyotp
# import qrcode
from PIL import Image, ImageFile, ImageSequence
from sqlalchemy.ext.mutable import MutableDict
import pypinyin
ImageFile.LOAD_TRUNCATED_IMAGES = True


pic_albums = db.Table(
    'pic_albums',
    db.Column('pic_id', db.Integer, db.ForeignKey('pic.id')),
    db.Column('album_id', db.Integer, db.ForeignKey('album.id')),
    db.Column('time', db.TIMESTAMP(), server_default=func.now()),
    UniqueConstraint('pic_id', 'album_id', name='unique_id')  # 联合唯一索引,name索引的名字
)


pic_tags = db.Table(
    'pic_tags',
    db.Column('pic_id', db.Integer, db.ForeignKey('pic.id')),
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('time', db.TIMESTAMP(), server_default=func.now()),
    UniqueConstraint('pic_id', 'tag_id', name='unique_id')  # 联合唯一索引,name索引的名字
)


pic_volumes = db.Table(
    'pic_volumes',
    db.Column('vol_id', db.Integer, db.ForeignKey('volume.id')),
    db.Column('pic_id', db.Integer, db.ForeignKey('pic.id')),
    db.Column('time', db.TIMESTAMP(), server_default=func.now()),
    db.Column('sort', db.SmallInteger, default=1),
    UniqueConstraint('vol_id', 'pic_id', name='unique_id')  # 联合唯一索引,name索引的名字
)

pic_actresses = db.Table(
    'pic_actresses',
    db.Column('pic_id', db.Integer, db.ForeignKey('pic.id')),
    db.Column('actress_id', db.Integer, db.ForeignKey('actress.id')),
    db.Column('time', db.TIMESTAMP(), server_default=func.now()),
    UniqueConstraint('pic_id', 'actress_id', name='unique_id')  # 联合唯一索引,name索引的名字
)


a_href = '&nbsp;<a href="{url}">{name}</a>'


class Pic(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    file_hash = db.Column(db.String(50), unique=True)  # 文件名，不带后缀
    file_type = db.Column(db.String(10))  # 文件后缀
    file_size = db.Column(db.Float(), index=True)  # 文件大小，单位KB
    width = db.Column(db.Integer())  # 图片宽度
    height = db.Column(db.Integer())  # 图片高度
    resolution = db.Column(db.Integer(), index=True)  # 分辨率
    create_time = db.Column(db.TIMESTAMP(), server_default=func.now())  # 创建时间
    note = db.Column(db.String(1000))  # 备注
    rate = db.Column(db.String(1), index=True, default=2)  # 评分
    pv = db.Column(db.Integer(), default=0)  # 浏览量
    info = db.Column(db.JSON())  # 记录图片其他信息
    delete_time = db.Column(db.TIMESTAMP(), default=None, nullable=True)  # 删除时间
    tags = db.relationship(
        'Tag',
        secondary=pic_tags,
        backref=db.backref('pics', lazy='dynamic')
    )
    albums = db.relationship(
        'Album',
        secondary=pic_albums,
        backref=db.backref('pics', lazy='dynamic')
    )
    volumes = db.relationship(
        'Volume',
        secondary=pic_volumes,
        backref=db.backref('pics', lazy='dynamic')
    )
    actresses = db.relationship(
        'Actress',
        secondary=pic_actresses,
        backref=db.backref('pics', lazy='dynamic')
    )
    label = 'pic'

    def __init__(self, file_hash, file_type, width, height, file_size, note):
        self.file_hash = file_hash
        self.file_type = file_type
        self.width = width
        self.height = height
        self.resolution = width * height
        self.file_size = file_size
        self.note = note

    def __repr__(self):
        return f'<PIC:{self.id}>'

    def pv_add(self):
        self.pv += 1
        db.session.commit()

    @property
    def file_name(self):
        return f'{self.file_hash}.{self.file_type}'

    @property
    def uri(self):
        # 本地
        # return '/'.join(['/piclab/pic', self.file_name[0:2], self.file_name])
        # 外部链接
        return get_image_url(self.file_name)

    def create_time_html(self):
        return f'<i class="fa fa-clock-o"></i>&nbsp;&nbsp;{time_str(self.create_time)}'

    @property
    def thumbnail_uri(self):
        # 本地
        # return '/'.join(['/piclab/sm', self.file_name[0:2], self.file_name])
        # 外部链接
        return get_thumb_url(self.file_hash)

    @property
    def thumbnail_img(self):
        return f'<img src="{self.thumbnail_uri}" style="transform:rotate({self.rotate}deg) scaleX({self.scalex})">'

    @property
    def thumbnail_img_archive(self):
        return f'<img src="{self.thumbnail_uri}" class="img-thumbnail img-responsive" style="transform:rotate({self.rotate}deg);">'

    @property
    def img(self):
        if self.file_type in ['webm', 'mp4', 'mov']:
            return f'<video src="{self.uri}" controls style="max-width:100%;max-height:100%;width:auto;height:auto;"></video>'
        return f'<img src="{self.uri}" style="transform:rotate({self.rotate}deg) scaleX({self.scalex});">'

    @property
    def full_name(self):
        """带后缀的文件名"""
        return self.file_name

    def resolution_hr(self):
        return f'{self.width}*{self.height}'

    def file_size_hr(self):
        if self.file_size < 1024:
            return f'{self.file_size}KB'
        else:
            return f'{round(self.file_size/1024, 2)}MB'

    @property
    def full_path(self):
        return Config.PICLAB_BASE_DIR / self.file_name[0:2] / self.file_name

    @property
    def full_thumb_path(self):
        return Config.PICLAB_THUMBNAIL_DIR / self.file_name[0:2] / self.file_name

    def remove_image(self):
        """删除图片文件"""
        delete_image_file(self.file_name)

    def new_rate(self, score):
        self.rate = score
        db.session.commit()

    def file_exist(self):
        img = self.full_path.exists()
        thumb = self.full_thumb_path.exists()
        return img, thumb

    def info_update(self, new_info):
        info_dict = dict()
        if self.info:
            info_dict.update(self.info)
        info_dict.update(new_info)
        self.info = info_dict
        db.session.commit()

    def info_get(self, key):
        return self.info.get(key)

    def info_del(self, key):
        info_dict = dict()
        info_dict.update(self.info)
        info_dict.pop(key, '')
        self.info = info_dict
        db.session.commit()

    @staticmethod
    def unfiled_pic_count():
        return Pic.query.filter(Pic.albums == None, Pic.actresses == None).count()

    @property
    def albums_id(self):
        return [album.id for album in self.albums]

    @property
    def tags_id(self):
        return [tag.id for tag in self.tags]

    @property
    def actresses_id(self):
        return [actress.id for actress in self.actresses]

    @property
    def volumes_id(self):
        return [vol.id for vol in self.volumes]

    def albums_html(self, blank=False):
        response = ['<i class="fa fa-newspaper-o"></i>']
        if self.albums:
            for alb in self.albums:
                response.append(a_href.format(url=url_for('pic.one_album', uid=alb.id, all=1), name=alb.name))
        elif blank:
            pass
        else:
            return ''
        return ''.join(response)

    def volumes_html(self, blank=False):
        response = ['<i class="fa fa-th-large"></i>']
        if self.volumes:
            for vol in self.volumes:
                response.append(a_href.format(url=url_for('pic.one_volume', uid=vol.id, all=1), name=vol.name))
        elif blank:
            pass
        else:
            return ''
        return ''.join(response)

    def tags_html(self, blank=False):
        response = ['<i class="fa fa-tags"></i>']
        if self.tags:
            for tag in self.tags:
                response.append(a_href.format(url=url_for('pic.one_tag', uid=tag.id), name=tag.name))
        elif blank:
            pass
        else:
            return ''
        return ''.join(response)

    def actresses_html(self, blank=False):
        response = ['<i class="fa fa-user-circle-o"></i>']
        if self.actresses:
            for actress in self.actresses:
                response.append(a_href.format(url=url_for('pic.one_actress', uid=actress.id, all=1), name=actress.name))
        elif blank:
            pass
        else:
            return ''
        return ''.join(response)

    def rate_html(self):
        return f'<span><i class="fa fa-star" style="color:#FFEE99"></i>&nbsp;{self.rate}</span>'

    def pv_html(self):
        return f'<span><i class="fa fa-thermometer-3"></i>&nbsp;{self.pv}</span>'

    def note_html(self):
        return f'<i class="fa fa-pencil-square-o"></i>&nbsp;<span class="album_pic_note" data-id="{self.id}">{self.note or ""}</span>'

    @property
    def rotate(self):
        rotate = 0
        if self.info and self.info.get('rotate'):
            rotate = self.info['rotate']
        return rotate

    @property
    def scalex(self):
        """图片缩放比例"""
        scalex = 1
        if self.info and self.info.get('scalex'):
            scalex = self.info['scalex']
        return scalex

    @property
    def file_type_label(self):
        if self.file_type == 'webp':
            label = 'label-success'
        elif self.file_type == 'png':
            label = 'label-info'
        elif self.file_type == 'jpg':
            label = 'label-warning'
        elif self.file_type == 'gif':
            label = 'label-danger'
        else:
            label = 'label-default'
        return f'<span class="label {label} file_type">{self.file_type}</span>'


class AlbumBaseModel(db.Model):
    """album/actress/tag/volume 公共类方法"""
    __abstract__ = True
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(100), unique=True)
    create_time = db.Column(db.TIMESTAMP(), server_default=func.now())  # 创建时间
    pv = db.Column(db.Integer(), default=0)  # 浏览量
    rate = db.Column(db.String(1), index=True, default=2)  # 评分
    cover_id = db.Column(db.Integer(), db.ForeignKey('pic.id', ondelete='SET NULL'))
    note = db.Column(db.String(1000))  # 备注
    pinyin = db.Column(db.String(128), default='')
    meta = db.Column(MutableDict.as_mutable(db.JSON), default={})
    label = ''
    fa_tag = ''

    def __init__(self, name):
        self.name = name
        self.update_pinyin(name)

    def __repr__(self):
        return f'<{self.label.title()}: {self.name}>'

    @property
    def standard_name(self):
        name = self.name + ('(已存档)' if self.meta.get('archived') else '')
        return name

    def update_pinyin(self, name):
        pinyin = pypinyin.lazy_pinyin(name, pypinyin.NORMAL, errors='ignore')
        self.pinyin = ''.join(pinyin)

    def pv_add(self):
        self.pv += 1
        db.session.commit()

    @property
    def cover(self):
        """给相册/标签/模特/卷获取封面"""
        if self.cover_id:
            cover_pic = Pic.query.get(self.cover_id)
            if cover_pic:
                return cover_pic.thumbnail_img
        if not self.pics.count():
            return '<img src="http://static5.piclab.org/image/photo.svg">'
        cover_pic = self.pics.all()[-1]
        return cover_pic.thumbnail_img

    def new_rate(self, score):
        self.rate = score
        db.session.commit()

    def name_html(self):
        """用于列表页展示名称"""
        return f'<i class="fa fa-{self.fa_tag}"></i>&nbsp;&nbsp;{self.name}'

    def note_html(self):
        if self.note:
            return self.note.replace('\n', '<br>')
        return ''

    def pv_html(self):
        return f'<span><i class="fa fa-thermometer-3"></i>&nbsp;{self.pv}</span>'

    def rate_html(self):
        return f'<span><i class="fa fa-star" style="color:#FFEE99"></i>&nbsp;{self.rate}</span>'

    def create_time_html(self):
        return f'<i class="fa fa-clock-o"></i>&nbsp;&nbsp;{time_str(self.create_time)}'

    def amount_html(self):
        return f'<span><i class="fa fa-list-ol"></i>&nbsp;{self.amount}</span>'

    @property
    def amount(self):
        """在缓存中取图片数量"""
        from .plugins.functions import site_info
        return site_info.get_cache(self.label, self.id).get('count', 0)

    @property
    def order_by(self):
        """排序字段"""
        return []

    @cached_property
    def pics_list(self):
        _p_list = self.pics.order_by(*self.order_by).values(Pic.id)
        return [p[0] for p in _p_list]

    @classmethod
    def paginator(cls, page, per_page_pics, filter_by_options: dict = None, load_all: int = 0, is_archived: str = None):
        if not filter_by_options:
            filter_by_options = {}
        if load_all:
            per_page_pics = 10000

        filter_options = []
        if is_archived == '1':
            filter_options.append(cls.meta['archived'].as_boolean() == True)
        elif is_archived == '0':
            filter_options.append(cls.meta['archived'].as_string() == None)
        return cls.query.filter_by(**filter_by_options).filter(*filter_options).order_by(desc(cls.id)).paginate(
            page=page, per_page=per_page_pics, error_out=False)

    def pic_paginator(self, page, per_page_pics, filter_options: list = None, load_all: bool = False):
        if not filter_options:
            filter_options = []
        if load_all:
            per_page_pics = 10000
        return self.pics.filter(Pic.delete_time==None, *filter_options).order_by(*self.order_by).paginate(page=page, per_page=per_page_pics, error_out=False)

    def update_meta(self, key, value):
        if value:
            self.meta[key] = value
        else:
            self.meta.pop(key, None)


class Actress(AlbumBaseModel):
    volumes = db.relationship(
        'Volume',
        backref='actress',
        lazy='dynamic'
    )
    label = 'actress'
    fa_tag = 'user-circle-o'

    @property
    def volume_amount(self):
        return self.volumes.count()

    @property
    def order_by(self):
        return getattr(ActressSort, self.meta.get('order_by', ''), ActressSort.note)


class Album(AlbumBaseModel):
    base_album_id = db.Column(db.Integer(), db.ForeignKey('base_album.id', ondelete='SET NULL'))
    label = 'album'
    fa_tag = 'newspaper-o'

    @property
    def order_by(self):
        return getattr(AlbumSort, self.meta.get('order_by', ''), AlbumSort.note)


class BaseAlbum(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(100), unique=True)  # 相册名
    create_time = db.Column(db.TIMESTAMP(), server_default=func.now())  # 创建时间
    cover_id = db.Column(db.Integer(), db.ForeignKey('pic.id', ondelete='SET NULL'))
    note = db.Column(db.String(1000))  # 备注
    albums = db.relationship(
        'Album',
        backref='base_album',
        lazy='dynamic'
    )


class Tag(AlbumBaseModel):
    rate = None
    label = 'tag'
    fa_tag = 'tags'

    @property
    def order_by(self):
        return getattr(TagSort, self.meta.get('order_by', ''), TagSort.album_time)


class Volume(AlbumBaseModel):
    name = db.Column(db.String(64))  # 卷名
    actress_id = db.Column(db.Integer(), db.ForeignKey('actress.id', ondelete='SET NULL'))
    label = 'volume'
    fa_tag = 'th-large'

    __table_args__ = (
        UniqueConstraint('name', 'actress_id', name='_name_actress_id'),
    )

    def __init__(self, name, actress_id):
        super().__init__(name)
        self.actress_id = actress_id

    @property
    def standard_name(self):
        return f'{self.actress.name}:{self.name}{"(已存档)" if self.meta.get("archived") else ""}'

    @property
    def pics_id(self):
        id_list = []
        for pic_id in self.pics.values(text('pic_id')):
            id_list.append(pic_id[0])
        return id_list

    def get_next(self, page):
        return []  # To Do

    @property
    def order_by(self):
        return getattr(VolumeSort, self.meta.get('order_by', ''), VolumeSort.note)


class Work(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(100), unique=True)
    content = db.Column(db.Text())
    priority = db.Column(db.Integer(), default=1)
    create_time = db.Column(db.TIMESTAMP(), server_default=func.now())
    update_time = db.Column(db.DateTime(), onupdate=func.now())

    def __init__(self, title, content, priority):
        self.title = title
        self.content = content
        self.priority = priority

    def content_br(self):
        return self.content.replace(os.linesep, '<br>')


class User(db.Model, UserMixin):
    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.String(20), unique=True)
    password = db.Column(db.String(200))
    totp_key = db.Column(db.String(32), nullable=True)

    def __init__(self, username, password, totp_key):
        """
        创建用户
        :param username:
        :param password:
        :param totp_key:
        """
        self.username = username
        self.password = generate_password_hash(password)
        self.totp_key = totp_key
        db.session.add(self)
        db.session.commit()

    def check_password(self, password):
        return check_password_hash(self.password, password)

    # def set_totp_key(self):
    #     self.totp_key = pyotp.random_base32()
    #     db.session.commit()

    # def totp(self):
    #     return pyotp.TOTP(self.totp_key, interval=30)

    # def qrcode(self):
    #     return qrcode.make(self.totp().provisioning_uri(self.username, issuer_name='Piclab'))

    # def check_totp(self, code):
    #     return self.totp().verify(code)


class Setting(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    label = db.Column(db.String(50), unique=True)
    desc = db.Column(db.String(50), unique=True)
    value = db.Column(db.String(100))

    def __init__(self, label, desc, value):
        self.label = label
        self.desc = desc
        self.value = value

    def update(self, value):
        self.value = value
        cache.hset(RedisKey.settings_hash, self.label, value)
        db.session.commit()

    @staticmethod
    def get(label):
        setting_value = cache.hget(RedisKey.settings_hash, label, content_type='str')
        if not setting_value:
            setting = Setting.query.filter_by(label=label).first()
            if setting:
                setting_value = setting.value
                cache.hset(RedisKey.settings_hash, label, setting_value)
            else:
                raise KeyError

        if setting_value.isdigit():
            setting_value = int(setting_value)
        return setting_value


class PicFile:

    def __init__(self, file_name, make_thumb=True):
        """
        图片初始化
        :param file_name: 文件名，只从temp目录取文件，不需要文件路径
        """
        self.file_path = Config.PICLAB_TEMP_DIR / file_name  # 图片原始名称和路径，带文件名和后缀的绝对路径
        self.img_buffer = None  # 生成的文件存在内存
        self.thumb_buffer = None  # 生成的文件存在内存
        self._receive_pic(file_name, make_thumb=make_thumb)

    def _receive_pic(self, file_name, make_thumb=True):
        """接收和处理图片"""
        if file_name.lower().endswith('gif'):
            # GIF文件转webp
            img_data = gif2webp(self.file_path, make_thumb=make_thumb)
            self.md5 = img_data.get('md5')
            self.width = img_data.get('width')
            self.height = img_data.get('height')
            self.file_size = img_data.get('file_size')
            self.img_buffer = img_data.get('img_buffer')
            self.thumb_buffer = img_data.get('thumb_buffer')
            self.file_type = '.webp'
            self.md5_name = f'{self.md5}.webp'
            self.md5_path = Path(self.md5[0:2]) / self.md5_name
            if make_thumb:
                self.make_thumbnail()
        else:
            self.file_size = round(os.path.getsize(self.file_path) / 1024, 2)
            with open(self.file_path, 'rb') as f:
                self.md5 = hashlib.md5(f.read()).hexdigest()
                with Image.open(f) as img:
                    self.width, self.height = img.size
                    self.file_type = '.' + ('jpg' if img.format == 'JPEG' else img.format.lower())
                    self.md5_name = self.md5 + self.file_type
                    self.md5_path = Path(self.md5[0:2]) / self.md5_name
                    if make_thumb:
                        self.make_thumbnail(img)

    # 归档
    def archive(self):
        # 移动到PIC_LAB
        try:
            self.archive_func()
        except:
            # 如果报错则尝试删除最终路径上的文件再移动
            os.remove(Config.PICLAB_BASE_DIR / self.md5_path)
            self.archive_func()

    def archive_func(self):
        if self.img_buffer:
            # 向归档目录存储文件
            with open(Config.PICLAB_BASE_DIR / self.md5_path, 'wb') as img:
                img.write(self.img_buffer.getvalue())
            os.remove(self.file_path)  # 删除原gif动图
        else:
            # 移动文件到归档目录
            move(self.file_path, Config.PICLAB_BASE_DIR / self.md5_path)

    def make_thumbnail(self, img: Image.Image = None):
        """生成缩略图"""
        thumbnail_path = Config.PICLAB_THUMBNAIL_DIR / self.md5_path

        if self.thumb_buffer:
            # 已经在内存中生成缩略图
            with open(thumbnail_path, 'wb') as f:
                f.write(self.thumb_buffer.getvalue())

        elif hasattr(img, 'is_animated') and img.is_animated:
            # webp动图
            thumb_sequence = []
            for frame in ImageSequence.Iterator(img):
                _frame = frame.copy().resize(self.thumbnail_size)
                thumb_sequence.append(_frame)
            thumb_sequence[0].save(thumbnail_path, save_all=True, quality=50, append_images=thumb_sequence[1: len(thumb_sequence) // 2])
        else:
            img.thumbnail(self.thumbnail_size)
            img.save(fp=thumbnail_path)

    @property
    def thumbnail_size(self):
        """
        缩略图尺寸
        :return: (200, 高度)
        """
        return get_thumbnail_size(self.width, self.height)


def time_str(dt):
    """把datetime类型的时间转成字符串时间"""
    if isinstance(dt, datetime):
        return dt.strftime('%Y-%m-%d')
    else:
        return dt


class AlbumSort:
    """相册排序"""
    note = [Pic.note, desc(Pic.id)]  # 按图片描述排序
    album_time = [desc(pic_albums.c.time), desc(pic_albums.c.pic_id)]  # 按图片加入相册的时间排序
    pic_time = [desc(Pic.id)]  # 按图片导入的时间排序


class TagSort(AlbumSort):
    """标签排序"""
    album_time = [desc(pic_tags.c.time), desc(pic_tags.c.pic_id)]


class ActressSort(AlbumSort):
    """模特排序"""
    album_time = [desc(pic_actresses.c.time), desc(pic_actresses.c.pic_id)]


class VolumeSort(AlbumSort):
    """卷排序"""
    album_time = [desc(pic_volumes.c.time), desc(pic_volumes.c.pic_id)]


models_mapping = {
    'pic': Pic,
    'album': Album,
    'actress': Actress,
    'tag': Tag,
    'volume': Volume,
}
